Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel

Public Class frmCompareStats

    Private Sub frmCompareStats_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        SaveControlValues(REGAPP, Me)
    End Sub

    Private Sub frmCompareStats_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        For i As Integer = 1 To 2
            Dim cbo As ComboBox = Choose(i, cboTableName1, cboTableName2)
            cbo.Sorted = True
            cbo.Items.Clear()
            For Each tbl As String In Project.GetTableList(clsProject.enumTableTypes.Working, clsProject.enumTableTypes.Master)
                cbo.Items.Add(tbl)
            Next
            cbo.Text = ActiveBrowser.TableName
        Next
        For i As Integer = 1 To 4
            With CType(Choose(i, dtStart1, dtEnd1, dtStart2, dtEnd2), DateTimePicker)
                .Format = DateTimePickerFormat.Custom
                .CustomFormat = MyDateFormat()
            End With
        Next
        chkIncludeBDLs.Checked = True
        cboConsolidate.SelectedIndex = 0
        GetControlValues(REGAPP, Me)
    End Sub

    Private Sub cboConsolidate_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboConsolidate.SelectedIndexChanged
        lblStationID1.Text = cboConsolidate.Text
        lblStationID2.Text = cboConsolidate.Text
        FillStationList(1)
        FillStationList(2)
    End Sub

    Private Sub FillStationList(ByVal DatasetNum As Integer)
        Try
            MainForm.WriteProgress("Refreshing consolidation lists", ProgressBarStyle.Marquee)
            Dim TableName As String = Choose(DatasetNum, cboTableName1.Text, cboTableName2.Text)
            Dim cbo As ComboBox = Choose(DatasetNum, cboStationID1, cboStationID2)
            Select Case cboConsolidate.SelectedIndex
                Case 0 'station id
                    Project.FillPickList(cbo, TableName, "Stations", "Station_ID", "Station_Name")
                Case 1 'branch id
                    Project.FillPickList(cbo, "", "Branches", "Branch_ID", "Branch_Name")
                Case Else '8 or 12 digit huc
                    cbo.Items.Clear()
                    Dim dr As DbDataReader = Project.DB.ExecuteReader($"SELECT DISTINCT Stations.HUC FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations ORDER BY HUC")
                    While dr IsNot Nothing AndAlso dr.Read
                        If Not dr.IsDBNull(0) Then
                            Dim HUC As String = dr.GetString(0)
                            If HUC.Length >= 8 AndAlso cboConsolidate.SelectedIndex = 2 Then
                                HUC = HUC.Substring(0, 8)
                            ElseIf HUC.Length >= 12 AndAlso cboConsolidate.SelectedIndex = 3 Then
                                HUC = HUC.Substring(0, 12)
                            Else
                                HUC = ""
                            End If
                            If HUC.Trim <> "" And Not cbo.Items.Contains(HUC) Then cbo.Items.Add(HUC)
                        End If
                    End While
                    dr.Close()
                    dr = Nothing
            End Select
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub cboTableName1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableName1.SelectedIndexChanged
        Try
            MainForm.WriteProgress("Refreshing data lists", ProgressBarStyle.Marquee)
            Dim TableName As String = cboTableName1.Text
            With Project
                FillStationList(1)
                .FillPickList(cboPCode1, TableName, "PCodes", "PCode", "Anal_Name")
                .FillPickList(cboCCode1, TableName, "CCodes", "CCode", "CCode_Name", "(All)")
                If TableName <> "" Then
                    dtStart1.Value = Max(dtStart1.MinDate, TestNull(.DB.ExecuteScalar("SELECT MIN(Date_Time) FROM {0}", TableName), dtStart1.MinDate))
                    dtEnd1.Value = Min(dtStart1.MaxDate, TestNull(.DB.ExecuteScalar("SELECT MAX(Date_Time) FROM {0}", TableName), dtEnd1.MaxDate))
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub cboTableName2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableName2.SelectedIndexChanged
        Try
            MainForm.WriteProgress("Refreshing data lists", ProgressBarStyle.Marquee)
            Dim TableName As String = cboTableName2.Text
            With Project
                FillStationList(2)
                .FillPickList(cboPCode2, TableName, "PCodes", "PCode", "Anal_Name")
                .FillPickList(cboCCode2, TableName, "CCodes", "CCode", "CCode_Name", "(All)")
                dtStart2.Value = Max(dtStart1.MinDate, TestNull(.DB.ExecuteScalar("SELECT MIN(Date_Time) FROM {0}", TableName), dtStart2.MinDate))
                dtEnd2.Value = Min(dtStart1.MaxDate, TestNull(.DB.ExecuteScalar("SELECT MAX(Date_Time) FROM {0}", TableName), dtEnd2.MaxDate))
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub btnOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOK.Click
        MainForm.WriteProgress("Starting Microsoft Excel...", ProgressBarStyle.Marquee)
        Dim IStartedExcel As Boolean = False
        Try
            app = GetObject(, "Excel.Application")
        Catch ex As Exception
            Try
                app = CType(CreateObject("Excel.Application"), Excel.Application)
                IStartedExcel = True
            Catch ex2 As Exception
                ErrorMsg("Unable to connect to Microsoft Excel application. Please check to make sure it is installed on your computer.")
                Exit Sub
            End Try
        End Try
        app.Visible = True
        app.WindowState = Excel.XlWindowState.xlNormal
        app.SheetsInNewWorkbook = 1
        wkb = app.Workbooks.Add
        If PerformAnalysis(XLName, chkIncludeBDLs.Checked, cboConsolidate.SelectedIndex, cboTableName1.Text, Project.GetPickList(cboStationID1), Project.GetPickList(cboPCode1), Project.GetPickList(cboCCode1), dtStart1.Value, dtEnd1.Value, cboTableName2.Text, Project.GetPickList(cboStationID2), Project.GetPickList(cboPCode2), Project.GetPickList(cboCCode2), dtStart2.Value, dtEnd2.Value) Then
            wkb.Activate()
            AppActivate(app.Caption)
            'wkb.Close()
            'app.Quit()
            Try
                'wkb.SaveAs()
            Catch ex As Exception
            End Try
            wkb = Nothing
            app = Nothing
        Else
            DialogResult = Windows.Forms.DialogResult.None
        End If
        MainForm.ProgressComplete()
    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "9")
    End Sub
End Class

''' <summary>
''' This project was originally developed in April 2004 by L.C. Wilson, Clayton Engineering
''' and J.C. Maze, URS Corporation to provide a link between Excel spreadsheets and an Access
''' database used to evaluate BMP performance for the Florida Department of Environmental Protection
''' It was enhanced in February 2005 to include the ability to evaluate the performance of BMPs
''' by analyzing mass loads (MASS table was added)
''' May 2005, MONITORINGCOST table added; various enhancements to the LOADS analysis
''' January 2006, EVENT_PAIRS table added; included robust handling of BDL values and enhanced analysis of
'''               removal efficiencies using event pairs
''' January 2008, Imported from Excel VBA module and adapted to WRDB 5
''' </summary>
''' <remarks>
''' Required reference: Microsoft Excel 9.0 Object library; 
''' Note that Excel may stay in the open process list because some global variables are referenced
''' See: http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/index.htm
''' </remarks>
Module modCompareStats

    Const Debugging As Boolean = True 'controls how trapped errors are reported
    Friend XLName As String 'name of XL workbook containing data in tabs, with full path and extension
    Friend app As Excel.Application
    Friend wkb As Excel.Workbook

    Private Structure StatType
        Dim Num As Integer
        Dim NumOutliers As Integer
        Dim NumBDL As Integer
        Dim Mean As Double
        Dim Median As Double
        Dim StDev As Double
        Dim CV As Double
        Dim Min As Double
        Dim Max As Double
        Dim Q10 As Double
        Dim Q25 As Double
        Dim Q75 As Double
        Dim Q90 As Double
        Dim IQR As Double
        Dim t95 As Double
        Dim StdError As Double
        Dim CI As Double
        Dim UCL As Double
        Dim LCL As Double
        Dim UFence As Double
        Dim LFence As Double
        Dim CoxUCL As Double
        Dim CoxLCL As Double
        Dim ArithEstMean As Double
        Dim ArithEstMedian As Double
        Dim ArithEstStDev As Double
        Dim ArithEstCV As Double
    End Structure

    Private Structure DataType
        Dim SampleNum As Integer
        Dim PlottingProb As Double
        Dim NormalProb As Double
        Dim DateTime As Date
        Dim Value As Double
        Dim DBValue As Double 'value in database
        Dim DL As Double
        Dim IsNonDetect As Boolean
        Dim Outlier As Boolean
    End Structure

    Friend Enum enumConsolidate
        StationID
        BranchID
        HUC8
        HUC12
    End Enum

    ''' <summary>
    ''' Perform the Acquire function for both the inflow and outflow monitoring stations
    ''' 2/28/05: Also will perform analysis of mass loads ("Acquire and Analyze Mass Loads" sheet)
    ''' 1/20/06: Now performs robust BDL analysis and analysis of event pairs
    ''' </summary>
    Friend Function PerformAnalysis(ByVal XLName As String, ByVal IncludeBDLs As Boolean, ByVal Consolidate As enumConsolidate, _
        ByVal TableName1 As String, ByVal Station_ID1 As String, ByVal PCode1 As String, ByVal CCode1 As String, ByVal MinDate1 As Date, ByVal MaxDate1 As Date, _
        ByVal TableName2 As String, ByVal Station_ID2 As String, ByVal PCode2 As String, ByVal CCode2 As String, ByVal MinDate2 As Date, ByVal MaxDate2 As Date) As Boolean

        Try
            Dim OutStats, InStats, InLogStats, OutLogStats As StatType
            Dim MyData As New DataObject
            Dim wks As Excel.Worksheet
            Dim InData() As DataType = Nothing
            Dim OutData() As DataType = Nothing
            Dim InSum, InSum2 As Double
            Dim OutSum, OutSum2 As Double
            Dim OutSS, Sum2, InSS, TotalSS As Double
            Dim df As Integer
            Dim F, MSBtwn, MSWthn, P As Double
            Dim i As Integer
            Dim IsDataTable As Boolean


            wkb = app.Workbooks.Add

            'set default font to Arial Narrow 10
            With wkb.Styles("Normal").Font
                .Name = "Arial Narrow"
                .Size = 9
            End With

            'parse selected items in list


            If Not AcquireData(TableName1, Consolidate, Station_ID1, PCode1, CCode1, MinDate1, MaxDate1, IncludeBDLs, "Dataset 1", InData, InStats, InLogStats) Then Return False

            If Not AcquireData(TableName2, Consolidate, Station_ID2, PCode2, CCode2, MinDate2, MaxDate2, IncludeBDLs, "Dataset 2", OutData, OutStats, OutLogStats) Then Return False

            'turn off updating to speed up macro

            app.StatusBar = "Please wait, updating Summary Stats worksheet..."
            MainForm.WriteProgress("Please wait, updating Summary Stats worksheet...", ProgressBarStyle.Marquee)
            app.ScreenUpdating = False

            '=================================================================================================
            'put summary data in spreadsheet
            '=================================================================================================

            If ObjectContains(wkb.Worksheets, "Summary Stats") Then
                wks = wkb.Worksheets("Summary Stats")
            Else
                wks = wkb.Worksheets.Add(, wkb.Worksheets(wkb.Worksheets.Count))
            End If

            With wks
                .Name = "Summary Stats"
                .StandardWidth = 12
                .Activate()
            End With

            '=================================================================================================
            'Page header...
            '=================================================================================================

            If Not PasteTable(wks, "Summary Statistics||||||||" & _
                            "<Dataset 1:|" & String.Format("Table={0}; Location ID={1}; PCode={2}; CCode={3}; Dates={4:d} to {5:d}", TableName1, Station_ID1, PCode1, CCode1, MinDate1, MaxDate1) & _
                            "<Dataset 2:|" & String.Format("Table={0}; Location ID={1}; PCode={2}; CCode={3}; Dates={4:d} to {5:d}", TableName2, Station_ID2, PCode2, CCode2, MinDate2, MaxDate2) & _
                            "<Criteria:|Include BDLs=" & IIf(IncludeBDLs, "Y", "N"), "A1", True) Then Return False

            'merge cells containing IDs
            For i = 2 To 4
                With wks.Range(wks.Cells.Item(i, 2), wks.Cells.Item(i, 9))
                    .Merge()
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                End With
            Next i

            '=================================================================================================
            'Summary Statistics table
            '=================================================================================================

            Dim RowComments As String
            RowComments = "Total Number of values extracted from database" & _
                          "|Number of values identified as below detection limits (BDL)" & _
                          "|Number of values that are above or below the fence values and considered outliers from the distribution" & _
                          "|Average value of the log-transformed values" & _
                          "|Standard deviation of the log-transformed values" & _
                          "|Minimum value of the log-transformed values" & _
                          "|Maximum value of the log-transformed values" & _
                          "|Estimated 10th percentile value of the log-transformed distribution" & _
                          "|Estimated 25th percentile value of the log-transformed distribution (aka First Quartile)" & _
                          "|Estimated 75th percentile value of the log-transformed distribution (aka Third Quartile)" & _
                          "|Estimated 90th percentile value of the log-transformed distribution" & _
                          "|Inner-quartile range: difference between the 25th and 75th percentiles of the log-transformed distribution" & _
                          "|Student's t at an alpha of 5%" & _
                          "|Standard error of estimate" & _
                          "|95% confidence interval about the mean (Q50) value of the log-transformed distribution" & _
                          "|Mean value of the log-transformed values minus the 95% CI value" & _
                          "|Mean value of the log-transformed values plus the 95% CI value" & _
                          "|Lower bounds for outliers (Q25 - 1.5 IQR)" & _
                          "|Upper bounds for outliers (Q75 + 1.5 IQR)"

            If Not PasteTable(wks, "Statistics for Log-transformed Data:<" & _
                            "Statistic|Dataset 1|Dataset 2<" & _
                            "Data Count|" & InLogStats.Num & "|" & OutLogStats.Num & "<" & _
                            "# BDL|" & InLogStats.NumBDL & "|" & OutLogStats.NumBDL & "<" & _
                            "# Outliers|" & InLogStats.NumOutliers & "|" & OutLogStats.NumOutliers & "<" & _
                            "Mean" & "|" & InLogStats.Mean & "|" & OutLogStats.Mean & "<" & _
                            "Std Dev" & "|" & InLogStats.StDev & "|" & OutLogStats.StDev & "<" & _
                            "Minimum" & "|" & InLogStats.Min & "|" & OutLogStats.Min & "<" & _
                            "Maximum" & "|" & InLogStats.Max & "|" & OutLogStats.Max & "<" & _
                            "Q10" & "|" & InLogStats.Q10 & "|" & OutLogStats.Q10 & "<" & _
                            "Q25" & "|" & InLogStats.Q25 & "|" & OutLogStats.Q25 & "<" & _
                            "Q75" & "|" & InLogStats.Q75 & "|" & OutLogStats.Q75 & "<" & _
                            "Q90" & "|" & InLogStats.Q90 & "|" & OutLogStats.Q90 & "<" & _
                            "IQR" & "|" & InLogStats.IQR & "|" & OutLogStats.IQR & "<" & _
                            "95% t" & "|" & InLogStats.t95 & "|" & OutLogStats.t95 & "<" & _
                            "Std Error" & "|" & InLogStats.StdError & "|" & OutLogStats.StdError & "<" & _
                            "95% CI" & "|" & InLogStats.CI & "|" & OutLogStats.CI & "<" & _
                            "Lower CL" & "|" & InLogStats.LCL & "|" & OutLogStats.LCL & "<" & _
                            "Upper CL" & "|" & InLogStats.UCL & "|" & OutLogStats.UCL & "<" & _
                            "Lower Fence" & "|" & InLogStats.LFence & "|" & OutLogStats.LFence & "<" & _
                            "Upper Fence" & "|" & InLogStats.UFence & "|" & OutLogStats.UFence, _
                            "A9", True, True, True, "Statistical summary of the dataset distributions", "|Dataset 1 statistics|Dataset 2 statistics", RowComments) Then Return False

            'reset number format for two cells
            With app.ActiveWindow.RangeSelection
                .Range(wks.Cells.Item(3, 2), wks.Cells.Item(5, 3)).NumberFormat = "General"
            End With

            '=================================================================================================
            'ANOVA table
            '=================================================================================================

            For i = 1 To UBound(InData)
                With InData(i)
                    If Not .Outlier Then
                        InSum = InSum + System.Math.Log(.Value)
                        InSum2 = InSum2 + System.Math.Log(.Value) ^ 2
                    End If
                End With
            Next i

            For i = 1 To UBound(OutData)
                With OutData(i)
                    If Not .Outlier Then
                        OutSum = OutSum + System.Math.Log(Max(.Value, 0.00001))
                        OutSum2 = OutSum2 + System.Math.Log(Max(.Value, 0.00001)) ^ 2
                    End If
                End With
            Next i

            Sum2 = InSum2 + OutSum2

            InSS = InSum2 - InSum ^ 2 / InStats.Num
            OutSS = OutSum2 - OutSum ^ 2 / OutStats.Num
            TotalSS = Sum2 - (InSum + OutSum) ^ 2 / (CDbl(InStats.Num) + OutStats.Num)
            MSBtwn = TotalSS - (OutSS + InSS)
            df = InStats.Num + OutStats.Num - 2
            MSWthn = (OutSS + InSS) / IIf(df = 0, 0.0001, df)
            F = MSBtwn / IIf(MSWthn = 0, 0.0001, MSWthn)
            If F <= 0 Then 'this can occur if all values are the same (-x.xx E-15)
                P = 999 'force it to fail test
            Else
                Try
                    P = app.WorksheetFunction.FDist(F, 1, IIf(df = 0, 1, df))
                Catch ex As Exception
                    P = 0
                End Try
            End If

            If Not PasteTable(wks, "ANOVA|LN(Dataset 1)|LN(Dataset 2)|All<" & "Sum|" & InSum & "|" & OutSum & "|" & InSum + OutSum & "<" & _
                            "Sum^2|" & InSum2 & "|" & OutSum2 & "|" & Sum2 & "<" & "Sum Sq.|" & InSS & "|" & OutSS & "<" & _
                            "Overall Mean|" & (InLogStats.Mean * InLogStats.Num + OutLogStats.Mean * OutLogStats.Num) / (InLogStats.Num + OutLogStats.Num), _
                            "F9", True, True, False, "", _
                            "Analysis of Variance Table: a parametric test used to determine whether the log-transformed mean from dataset 1 is equal to that of dataset 2|Statistics for log-transformed dataset 1|Statistics for log-transformed dataset 2|Statistics for all data together", _
                            "Sum of log-transformed data" & _
                            "|Square of sum" & _
                            "|Sum of squares" & _
                            "|Mean of dataset 1 and dataset 2 log-transformed data together") Then Return False

            If Not PasteTable(wks, "Source|SS|df|MS<" & "Btwn groups|" & TotalSS - (OutSS + InSS) & "|1|" & MSBtwn & "<" & _
                            "Wthn groups|" & OutSS + InSS & "|" & df & "|" & MSWthn & "<" & _
                            "Total|" & TotalSS & "<" & _
                            "F|" & F & "|" & IIf(P > 0.05, "FAILS: NO DIFFERENCE IN MEANS", "PASS: MEANS ARE DIFFERENT") & "<" & _
                            "P|" & P & "<", "F14", True, True, False, "", _
                            "Analysis of Variance Table (continued): a parametric test used to determine whether the log-transformed mean value of dataset 1 is equal to that of dataset 2|Sum of squares|Degrees of freedom|Mean sum of squares", _
                            "Statistics between dataset 1 and dataset 2 series" & _
                            "|Statistics within each series" & _
                            "|Total sum of squares" & _
                            "|Computed F statistic to evaluate if means are different = MS Between/MS Within" & _
                            "|Probability that the dataset 1 and 2 distributions have the same mean value (statistically)") Then Return False


            With app.ActiveWindow.RangeSelection.Range(wks.Cells.Item(5, 3), wks.Cells.Item(6, 4))
                .Merge()
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .Font.Bold = True
                .WrapText = True
                .FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "PASS: MEANS ARE DIFFERENT").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Lime)
                .FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "FAILS: NO DIFFERENCE IN MEANS").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
            End With

            '=================================================================================================
            'percent reduction table
            '=================================================================================================

            If Not PasteTable(wks, "Effluent Probability Method<" & _
                            "||Dataset 1|Dataset 2<" & _
                            "Arithmetic Est. of Mean:||" & InLogStats.ArithEstMean & "|" & OutLogStats.ArithEstMean & "<" & _
                            "Modified Cox 95% UCL:||" & System.Math.Exp(InLogStats.CoxUCL) & "|" & System.Math.Exp(OutLogStats.CoxUCL) & "<" & _
                            "Modified Cox 95% LCL:||" & System.Math.Exp(InLogStats.CoxLCL) & "|" & System.Math.Exp(OutLogStats.CoxLCL) & "<" & _
                            "Average Difference:|||" & (InLogStats.ArithEstMean - OutLogStats.ArithEstMean) / InLogStats.ArithEstMean & "<" & _
                            "Max. Diff. (Dataset 1 UCL - Dataset 2 LCL):|||" & (System.Math.Exp(InLogStats.CoxUCL) - System.Math.Exp(OutLogStats.CoxLCL)) / System.Math.Exp(InLogStats.CoxUCL) & "<" & _
                            "Min. Diff. (Dataset 1 LCL - Dataset 2 UCL):|||" & (System.Math.Exp(InLogStats.CoxLCL) - System.Math.Exp(OutLogStats.CoxUCL)) / System.Math.Exp(InLogStats.CoxLCL), _
                            "F22", True, True, True, _
                            "This table compares the dataset 1 and 2 distributions as independent and ignores any timing or correlation between the two.", _
                            "||Dataset 1 statistics|Dataset 2 statistics", "Estimate of the arithmetic mean value (assuming a log-normal distribution)" & _
                            "|Upper 95% confidence limit about the estimated mean (using the Modified Cox method)" & _
                            "|Lower 95% confidence limit about the estimated mean (using the Modified Cox method)" & _
                            "|Estimated average percent reduction" & _
                            "|Upper limit of estimated average percent reduction." & _
                            "|Lower limit of estimated average percent reduction.") Then Return False

            'merge cells for longer row headings
            With app.ActiveWindow.RangeSelection
                For i = 1 To 7
                    With .Range(wks.Cells.Item(i + 1, 1), wks.Cells.Item(i + 1, IIf(i <= 4, 2, 3)))
                        .Merge()
                        .HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                    End With
                Next i
                .Range(wks.Cells.Item(6, 4), wks.Cells.Item(8, 4)).NumberFormat = "0.0%"
            End With

            '=================================================================================================
            'compute series to facilitate log-probability plotting
            '=================================================================================================

            Dim OutMin, InMin, InMax, OutMax As Double

            InMin = 1 / (InStats.Num + 1.0#) : InMax = InStats.Num / (InStats.Num + 1.0#)
            OutMin = 1 / (OutStats.Num + 1.0#) : OutMax = OutStats.Num / (OutStats.Num + 1.0#)

            If Not PasteTable(wks, _
                "Probability Plots<" & _
                "Dataset 1 Val|Dataset 1 SD|Dataset 2 Val|Dataset 2 SD<" & _
                Exp_NormInv(InMin, InLogStats.Mean, InLogStats.StDev) & "|" & app.WorksheetFunction.NormSInv(InMin) & "|" & Exp_NormInv(OutMin, OutLogStats.Mean, OutLogStats.StDev) & "|" & app.WorksheetFunction.NormSInv(OutMin) & "<" & _
                Exp_NormInv(InMax, InLogStats.Mean, InLogStats.StDev) & "|" & app.WorksheetFunction.NormSInv(InMax) & "|" & Exp_NormInv(OutMax, OutLogStats.Mean, OutLogStats.StDev) & "|" & app.WorksheetFunction.NormSInv(OutMax) & "<", _
                "A53", False, True, True, "Data used to plot 'best fit' line in the Probability chart (based on method of moments)") Then Return False

            '=================================================================================================
            'compute series to facilitate box and whisker plotting
            '=================================================================================================

            If Not PasteTable(wks, _
                "Box and Whisker Plots" & _
                "<Dataset 1 X|Dataset 1 Y|Dataset 2 X|Dataset 2 Y" & _
                "<0.8|" & System.Math.Exp(InLogStats.Q75) & _
                "|1.8|" & System.Math.Exp(OutLogStats.Q75) & _
                "<1.2|" & System.Math.Exp(InLogStats.Q75) & _
                "|2.2|" & System.Math.Exp(OutLogStats.Q75) & _
                "<1.2|" & System.Math.Exp(InLogStats.UCL) & _
                "|2.2|" & System.Math.Exp(OutLogStats.UCL) & _
                "<1.1|" & System.Math.Exp(InLogStats.Mean) & _
                "|2.1|" & System.Math.Exp(OutLogStats.Mean) & _
                "<1.2|" & System.Math.Exp(InLogStats.LCL) & _
                "|2.2|" & System.Math.Exp(OutLogStats.LCL) & _
                "<1.2|" & System.Math.Exp(InLogStats.Q25) & _
                "|2.2|" & System.Math.Exp(OutLogStats.Q25) & _
                "<0.8|" & System.Math.Exp(InLogStats.Q25) & _
                "|1.8|" & System.Math.Exp(OutLogStats.Q25) & _
                "<0.8|" & System.Math.Exp(InLogStats.LCL) & _
                "|1.8|" & System.Math.Exp(OutLogStats.LCL) & _
                "<0.9|" & System.Math.Exp(InLogStats.Mean) & _
                "|1.9|" & System.Math.Exp(OutLogStats.Mean) & _
                "<0.8|" & System.Math.Exp(InLogStats.UCL) & _
                "|1.8|" & System.Math.Exp(OutLogStats.UCL) & _
                "<0.8|" & System.Math.Exp(InLogStats.Q75) & _
                "|1.8|" & System.Math.Exp(OutLogStats.Q75), _
                "A58", False, True, True, "Data used to plot the notched body outlines on the Box & Whisker chart") Then Return False

            If Not PasteTable(wks, _
                "Fences:" & _
                "<Dataset 1 X|Dataset 1 Y|Dataset 2 X|Dataset 2 Y" & _
                "<1|" & System.Math.Exp(InLogStats.UFence) & _
                "|2|" & System.Math.Exp(OutLogStats.UFence) & _
                "<1|" & System.Math.Exp(InLogStats.LFence) & _
                "|2|" & System.Math.Exp(OutLogStats.LFence), _
                "A72", False, True, True, "Data used to plot the upper and lower fence lines on the Box & Whisker chart") Then Return False

            Dim Results As New System.Text.StringBuilder("Dataset 1 Outliers:<Dataset 1 X|Dataset 1 Y<")

            For i = 1 To UBound(InData)
                With InData(i)
                    If .Outlier Then
                        Results.Append("1|" & .Value & "<")
                    End If
                End With
            Next i

            If Not PasteTable(wks, Results.ToString, "A77", False, True, True, "Data used to plot the identified outlier values for Dataset 1 in the Box & Whisker chart") Then Return False

            Results = New System.Text.StringBuilder("Dataset 2 Outliers:" & "<Dataset 2 X|Dataset 2 Y<")

            For i = 1 To UBound(OutData)
                With OutData(i)
                    If .Outlier Then
                        Results.Append("2|" & .Value & "<")
                    End If
                End With
            Next i

            If Not PasteTable(wks, Results.ToString, "C77", False, True, True, "Data used to plot the identified outlier values for Dataset 1 in the Box & Whisker chart") Then Return False

            Dim Units As String = ""

            '=================================================================================================
            'create charts
            '=================================================================================================

            PlotBoxWhisker(Station_ID1, Station_ID2, PCode1, PCode2, Units)
            PlotLogProbability(Station_ID1, Station_ID2, PCode1, PCode2, Units)
            PlotTimeSeries(Station_ID1, Station_ID2, PCode1, PCode2, Units)

            'format worksheet for printing

            With wks.PageSetup
                .Orientation = Excel.XlPageOrientation.xlPortrait
                .PrintArea = "A1:I42"
                .PrintHeadings = False
                .CenterHorizontally = True
                .FooterMargin = app.InchesToPoints(0.5)
                .LeftFooter = "&07WRDB Version " & My.Application.Info.Version.ToString
                .CenterFooter = "&07&D &T"
                .RightFooter = "&07&F [&A]"
            End With

Finished:

            'delete "Sheetx" empty sheets
            app.DisplayAlerts = False
            For Each wks In wkb.Worksheets
                If wks.Name Like "Sheet?" Then wks.Delete()
            Next wks
            app.DisplayAlerts = True

            If Not IsDataTable Then
                With wkb.Worksheets("Summary Stats")
                    .Move(before:=wkb.Charts("Box & Whisker"))
                    .Range("A1").Select()
                    .Activate()
                End With
            End If

            Return True
            'MsgBox("The worksheet has been saved to the file " & wkb.FullName, MsgBoxStyle.Information, "Notice")
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            app.ScreenUpdating = True
            app.StatusBar = False
            MainForm.ProgressComplete()
        End Try
    End Function

    ''' <summary>
    ''' Compute inverse normal deviate for log-transformed data Called by AcquireMDB
    ''' </summary>
    Private Function Exp_NormInv(ByRef Probability As Double, ByRef Mean As Double, ByRef StDev As Double) As Double
        Try
            If StDev = 0 Then
                Exp_NormInv = System.Math.Exp(Mean)
            Else
                Exp_NormInv = System.Math.Exp(app.WorksheetFunction.NormInv(Probability, Mean, StDev))
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Function

    ''' <summary>
    ''' create box and whisker plot; Called by AcquireMDB
    ''' </summary>
    Private Sub PlotBoxWhisker(ByVal Station_ID1 As String, ByVal Station_ID2 As String, ByVal PCode1 As String, ByVal PCode2 As String, Optional ByRef Units As String = "")
        Try
            Dim cht As Excel.Chart
            Dim ser As Excel.Series
            Dim axs As Excel.Axis
            Dim DataRange As Excel.Range

            app.ScreenUpdating = True
            app.StatusBar = "Please wait, plotting box & whisker..."
            MainForm.WriteProgress("Please wait, plotting box & whisker...", ProgressBarStyle.Marquee)
            app.ScreenUpdating = False

            If ObjectContains(wkb.Charts, "Box & Whisker") Then
                cht = wkb.Charts("Box & Whisker")
            Else
                cht = wkb.Charts.Add(Before:=wkb.Worksheets("Dataset 1"))
            End If

            Dim i As Integer
            With cht
                .Name = "Box & Whisker"
                .ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers
                .SizeWithWindow = True
                '.PlotArea.Fill.Visible = Office.MsoTriState.msoFalse
                .HasLegend = False
                .Activate()

                'change this statement if tables get moved
                DataRange = wkb.Worksheets("Summary Stats").Range("A58").CurrentRegion

                'add series for Box and Whisker body...

                ser = cht.SeriesCollection.NewSeries
                With ser
                    .Name = "Dataset 1"
                    .XValues = DataRange.Range("A3:A13")
                    .Values = DataRange.Range("B3:B13")
                    .Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                    .Border.Weight = 3
                End With
                ser = cht.SeriesCollection.NewSeries
                With ser
                    .Name = "Dataset 2"
                    .XValues = DataRange.Range("C3:C13")
                    .Values = DataRange.Range("D3:D13")
                    .Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                    .Border.Weight = 3
                    .Border.LineStyle = Excel.XlLineStyle.xlDash
                End With

                'add series for Box and Whisker fences...

                DataRange = wkb.Worksheets("Summary Stats").Range("A72").CurrentRegion

                ser = cht.SeriesCollection.NewSeries
                With ser
                    .Name = "InBWFence"
                    .XValues = DataRange.Range("A3:A4")
                    .Values = DataRange.Range("B3:B4")
                    .Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                    .Border.Weight = 3
                    .ChartType = Excel.XlChartType.xlXYScatterLines
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash
                    .MarkerSize = 8
                    .MarkerBackgroundColorIndex = Excel.XlColorIndex.xlColorIndexNone
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With
                ser = cht.SeriesCollection.NewSeries
                With ser
                    .Name = "OutBWFence"
                    .XValues = DataRange.Range("C3:C4")
                    .Values = DataRange.Range("D3:D4")
                    .Border.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                    .Border.Weight = 3
                    .Border.LineStyle = Excel.XlLineStyle.xlDash
                    .ChartType = Excel.XlChartType.xlXYScatterLines
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash
                    .MarkerSize = 8
                    .MarkerBackgroundColorIndex = Excel.XlColorIndex.xlColorIndexNone
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With

                'add series for Box and Whisker outliers...

                DataRange = wkb.Worksheets("Summary Stats").Range("A77").CurrentRegion

                If DataRange.Range("A3")._Default IsNot Nothing Then
                    ser = cht.SeriesCollection.NewSeries
                    With ser
                        .Name = "InBWOutliers"
                        .XValues = DataRange.Range("A3:A10")
                        .Values = DataRange.Range("B3:B10")
                        .ChartType = Excel.XlChartType.xlXYScatter
                        .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle
                        .MarkerSize = 8
                        .MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                        .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                    End With
                End If
                If DataRange.Range("C3")._Default IsNot Nothing Then
                    ser = cht.SeriesCollection.NewSeries
                    With ser
                        .Name = "OutBWOutliers"
                        .XValues = DataRange.Range("C3:C10")
                        .Values = DataRange.Range("D3:D10")
                        .ChartType = Excel.XlChartType.xlXYScatter
                        .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle
                        .MarkerSize = 8
                        .MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                        .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                    End With
                End If

                'setup axes...

                axs = .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = False
                    .HasMajorGridlines = False
                    .HasMajorGridlines = False
                    .MajorTickMark = Excel.XlTickMark.xlTickMarkNone
                    .MinorTickMark = Excel.XlTickMark.xlTickMarkNone
                    .TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone
                    .MinimumScale = 0.5
                    .MaximumScale = 2.5
                End With

                axs = .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = True
                    .AxisTitle.Caption = "Event Mean Concentration" & IIf(Units = "", "", " (" & Units & ")")
                    .HasMajorGridlines = True
                    .HasMinorGridlines = True
                    .HasDisplayUnitLabel = True
                    .TickLabels.NumberFormat = "General"
                    .ScaleType = Excel.XlScaleType.xlScaleLogarithmic
                    .MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlContinuous
                    .MinorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot
                End With

                'setup chart...

                .HasTitle = True
                .ChartTitle.Text = StringFormat("Dataset 1: Location ID={0}; PCode={1}\nDataset 2: Location ID={2}; PCode={3}", Station_ID1, PCode1, Station_ID2, PCode2)
                .ChartTitle.Font.Bold = True
                .ChartTitle.Font.Size = 12

                .HasLegend = True
                .Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
                .Legend.LegendEntries(1).Delete()

                For i = .Legend.LegendEntries.count To 3 Step -1
                    .Legend.LegendEntries(i).Delete()
                Next i

                .Deselect()

                'format chart for printing

                With .PageSetup
                    .Orientation = Excel.XlPageOrientation.xlLandscape
                    .ChartSize = Excel.XlObjectSize.xlFitToPage
                    .LeftMargin = app.InchesToPoints(0.5)
                    .RightMargin = app.InchesToPoints(0.5)
                    .TopMargin = app.InchesToPoints(0.5)
                    .BottomMargin = app.InchesToPoints(0.75)
                    .FooterMargin = app.InchesToPoints(0.5)
                    .LeftFooter = "&07WRDB Version " & My.Application.Info.Version.ToString
                    .CenterFooter = "&07&D &T"
                    .RightFooter = "&07&F [&A]"
                End With

            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    ''' <summary>
    ''' Create log probability chart
    ''' </summary>
    Private Sub PlotLogProbability(ByVal Station_ID1 As String, ByVal Station_ID2 As String, ByVal PCode1 As String, ByVal PCode2 As String, Optional ByRef Units As String = "")
        Try
            Dim cht As Excel.Chart
            Dim ser As Excel.Series
            Dim axs As Excel.Axis
            Dim DataRange As Excel.Range

            app.ScreenUpdating = True
            app.StatusBar = "Please wait, plotting log-probability..."
            MainForm.WriteProgress("Please wait, plotting log-probability...", ProgressBarStyle.Marquee)
            app.ScreenUpdating = False

            If ObjectContains(wkb.Charts, "Probability") Then
                cht = wkb.Charts("Probability")
            Else
                cht = wkb.Charts.Add(Before:=wkb.Worksheets("Dataset 1"))
            End If

            With cht
                .Name = "Probability"
                .ChartType = Excel.XlChartType.xlXYScatter
                .SizeWithWindow = True
                '.PlotArea.Fill.Visible = Microsoft.Office.MsoTriState.msoFalse
                .DisplayBlanksAs = Excel.XlDisplayBlanksAs.xlZero 'to eliminate problem with initial series
                For Each ser In .SeriesCollection
                    ser.Delete()
                Next ser
                .Activate()

                'add series for inflow and outflow plotting probabilities...
                'note: first series will still exist; reuse it

                ser = cht.SeriesCollection.NewSeries
                DataRange = wkb.Worksheets("Dataset 1").Range("A29").CurrentRegion
                With ser
                    .Name = "Dataset 1"
                    .Values = DataRange.Range("F3:F" & DataRange.Rows.Count)
                    .XValues = DataRange.Range("I3:I" & DataRange.Rows.Count)
                    .ChartType = Excel.XlChartType.xlXYScatter
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDiamond
                    .MarkerSize = 8
                    .MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With
                ser = cht.SeriesCollection.NewSeries
                DataRange = wkb.Worksheets("Dataset 2").Range("A29").CurrentRegion
                With ser
                    .Name = "Dataset 2"
                    .Values = DataRange.Range("F3:F" & DataRange.Rows.Count)
                    .XValues = DataRange.Range("I3:I" & DataRange.Rows.Count)
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleX
                    .MarkerSize = 8
                    .MarkerBackgroundColorIndex = Excel.XlColorIndex.xlColorIndexNone
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With

                'add series for inflow and outflow distribution fits...

                ser = cht.SeriesCollection.NewSeries
                DataRange = wkb.Worksheets("Summary Stats").Range("A53").CurrentRegion
                With ser
                    .Name = "Dataset 1 Fit"
                    .Values = DataRange.Range("A3:A4")
                    .XValues = DataRange.Range("B3:B4")
                    .ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers
                    With .Border
                        .Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                    End With
                End With
                ser = cht.SeriesCollection.NewSeries
                With ser
                    .Name = "Dataset 2 Fit"
                    .Values = DataRange.Range("C3:C4")
                    .XValues = DataRange.Range("D3:D4")
                    .ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers
                    With .Border
                        .Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                        .LineStyle = Excel.XlLineStyle.xlDash
                        .Weight = Excel.XlBorderWeight.xlMedium
                    End With
                End With

                'setup axes...

                axs = .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = True
                    .AxisTitle.Caption = "Event Mean Concentration" & IIf(Units = "", "", " (" & Units & ")")
                    .HasMajorGridlines = True
                    .HasMinorGridlines = True
                    .HasDisplayUnitLabel = True
                    .TickLabels.NumberFormat = "General"
                    .ScaleType = Excel.XlScaleType.xlScaleLogarithmic
                    .MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlContinuous
                    .MinorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot
                    .CrossesAt = .MinimumScale
                End With

                axs = .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = True
                    .AxisTitle.Caption = "Standard Normal Deviate"
                    .HasMajorGridlines = True
                    .HasMinorGridlines = False
                    .HasDisplayUnitLabel = True
                    .TickLabels.NumberFormat = "General"
                    .ScaleType = Excel.XlScaleType.xlScaleLinear
                    .MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlContinuous
                    .CrossesAt = .MinimumScale
                End With

                'setup chart...

                .HasTitle = True
                .ChartTitle.Text = StringFormat("Dataset 1: Location ID={0}; PCode={1}\nDataset 2: Location ID={2}; PCode={3}", Station_ID1, PCode1, Station_ID2, PCode2)
                .ChartTitle.Font.Bold = True
                .ChartTitle.Font.Size = 12
                .HasLegend = True
                .Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom

                'format chart for printing

                With .PageSetup
                    .Orientation = Excel.XlPageOrientation.xlLandscape
                    .ChartSize = Excel.XlObjectSize.xlFitToPage
                    .LeftMargin = app.InchesToPoints(0.5)
                    .RightMargin = app.InchesToPoints(0.5)
                    .TopMargin = app.InchesToPoints(0.5)
                    .BottomMargin = app.InchesToPoints(0.75)
                    .FooterMargin = app.InchesToPoints(0.5)
                    .LeftFooter = "&07WRDB Version " & My.Application.Info.Version.ToString
                    .CenterFooter = "&07&D &T"
                    .RightFooter = "&07&F [&A]"
                End With

                .Deselect()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    ''' <summary>
    ''' Create time series chart
    ''' </summary>
    Private Sub PlotTimeSeries(ByVal Station_ID1 As String, ByVal Station_ID2 As String, ByVal PCode1 As String, ByVal PCode2 As String, Optional ByRef Units As String = "")
        Try
            Dim cht As Excel.Chart
            Dim ser As Excel.Series
            Dim axs As Excel.Axis
            Dim DataRange As Excel.Range

            app.ScreenUpdating = True
            app.StatusBar = "Please wait, plotting time series..."
            MainForm.WriteProgress("Please wait, plotting time series...", ProgressBarStyle.Marquee)
            app.ScreenUpdating = False

            If ObjectContains(wkb.Charts, "Time Series") Then
                cht = wkb.Charts("Time Series")
            Else
                cht = wkb.Charts.Add(Before:=wkb.Worksheets("Dataset 1"))
            End If

            With cht
                .Name = "Time Series"
                .ChartType = Excel.XlChartType.xlXYScatter
                .SizeWithWindow = True
                '.PlotArea.Fill.Visible = Office.MsoTriState.msoFalse
                .DisplayBlanksAs = Excel.XlDisplayBlanksAs.xlZero 'to eliminate problem with initial series
                For Each ser In .SeriesCollection
                    ser.Delete()
                Next ser
                .Activate()

                'add series for inflow and outflow plotting probabilities...
                'note: first series will still exist; reuse it

                ser = cht.SeriesCollection.NewSeries
                DataRange = wkb.Worksheets("Dataset 1").Range("A29").CurrentRegion
                With ser
                    .Name = "Dataset 1"
                    .XValues = DataRange.Range("B3:B" & DataRange.Rows.Count)
                    .Values = DataRange.Range("C3:C" & DataRange.Rows.Count)
                    .ChartType = Excel.XlChartType.xlXYScatter
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDiamond
                    .MarkerSize = 8
                    .MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With

                ser = cht.SeriesCollection.NewSeries
                DataRange = wkb.Worksheets("Dataset 2").Range("A29").CurrentRegion
                With ser
                    .Name = "Dataset 2"
                    .XValues = DataRange.Range("B3:B" & DataRange.Rows.Count)
                    .Values = DataRange.Range("C3:C" & DataRange.Rows.Count)
                    .MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleX
                    .MarkerSize = 8
                    .MarkerBackgroundColorIndex = Excel.XlColorIndex.xlColorIndexNone
                    .MarkerForegroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
                End With

                'setup axes...

                axs = .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = True
                    .AxisTitle.Caption = "Date"
                    .HasMajorGridlines = True
                    .HasMinorGridlines = False
                    .HasDisplayUnitLabel = True
                    .TickLabels.NumberFormat = MyDateFormat.ToLower
                    .ScaleType = Excel.XlScaleType.xlScaleLinear
                    .MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlContinuous
                End With

                axs = .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)
                With axs
                    .HasTitle = True
                    .AxisTitle.Caption = "Event Mean Concentration" & IIf(Units = "", "", " (" & Units & ")")
                    .HasMajorGridlines = True
                    .HasMinorGridlines = True
                    .HasDisplayUnitLabel = True
                    .TickLabels.NumberFormat = "General"
                    .ScaleType = Excel.XlScaleType.xlScaleLogarithmic
                    .MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlContinuous
                    .MinorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot
                    .CrossesAt = .MinimumScale
                End With

                'setup chart...

                .HasTitle = True
                .ChartTitle.Text = StringFormat("Dataset 1: Location ID={0}; PCode={1}\nDataset 2: Location ID={2}; PCode={3}", Station_ID1, PCode1, Station_ID2, PCode2)
                .ChartTitle.Font.Bold = True
                .ChartTitle.Font.Size = 12
                .HasLegend = True
                .Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom

                'format chart for printing

                With .PageSetup
                    .Orientation = Excel.XlPageOrientation.xlLandscape
                    .ChartSize = Excel.XlObjectSize.xlFitToPage
                    .LeftMargin = app.InchesToPoints(0.5)
                    .RightMargin = app.InchesToPoints(0.5)
                    .TopMargin = app.InchesToPoints(0.5)
                    .BottomMargin = app.InchesToPoints(0.75)
                    .FooterMargin = app.InchesToPoints(0.5)
                    .LeftFooter = "&07WRDB Version " & My.Application.Info.Version.ToString
                    .CenterFooter = "&07&D &T"
                    .RightFooter = "&07&F [&A]"
                End With

                .Deselect()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    ''' <summary>
    ''' Query database tables for water quality data according to the selected
    ''' site ID, monitoring station IDs, and Parameter code and assemble so the
    ''' data can be analyzed
    ''' </summary>
    Private Function AcquireData(ByVal TableName As String, ByVal Consolidate As enumConsolidate, ByVal Station_ID As String, ByVal PCode As String, ByVal CCode As String, ByVal MinDate As Date, ByVal MaxDate As Date, ByVal IncludeBDLs As Boolean, ByVal SheetName As String, ByRef Data() As DataType, ByRef Stats As StatType, ByRef LogStats As StatType, Optional ByRef BMPID As String = "") As Boolean
        Try
            'this will now analyze for both Conc and Mass Load analyses; for latter, include non-blank BMPID value
            '(in which case, many of the passed parameters are ignored--IgnoreFlows through MediaTypes)
            'for Mass Loads, the FlowTypes parameter refers to the Mass Load Type (0 for input, 1 for loss)

            Dim Results As System.Text.StringBuilder
            Dim i, NumData As Integer
            Dim Prob As Double
            Dim wks As Excel.Worksheet
            Dim SortedData() As DataType

            'parse selected items in list

            app.StatusBar = "Please wait; acquiring data for " & Station_ID & "..."
            MainForm.WriteProgress(StringFormat("Please wait; acquiring data for Table={0}, Location ID= {1}, PCode={2}, CCode={3}, Min Date={4}, Max Date={5}...", TableName, Station_ID, PCode, CCode, MinDate, MaxDate), ProgressBarStyle.Marquee)
            System.Windows.Forms.Application.DoEvents()
            app.ScreenUpdating = False

            AcquireData = True

            MaxDate = MaxDate.AddDays(1).AddMinutes(-1) 'get all data on the last day

            'get station list for selected consolidation
            Dim StationList As String = ""
            Select Case Consolidate
                Case enumConsolidate.StationID : StationList = "'" & Station_ID & "'" 'just the single ID
                Case enumConsolidate.BranchID
                    Dim dr1 As DbDataReader = Project.DB.ExecuteReader($"SELECT DISTINCT Station_ID FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations WHERE Branch_ID='{0}'", Station_ID)
                    While dr1 IsNot Nothing AndAlso dr1.Read
                        StationList &= IIf(StationList = "", "", ",") & "'" & TestNull(dr1.GetValue(0), "") & "'"
                    End While
                Case Else
                    Dim dr1 As DbDataReader = Project.DB.ExecuteReader($"SELECT DISTINCT Station_ID,HUC FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations")
                    While dr1 IsNot Nothing AndAlso dr1.Read
                        Dim HUC As String = TestNull(dr1.GetValue(1), "")
                        If (Consolidate = enumConsolidate.HUC8 AndAlso HUC.Length >= 8 AndAlso Station_ID = HUC.Substring(0, 8)) OrElse _
                           (Consolidate = enumConsolidate.HUC12 AndAlso HUC.Length >= 12 AndAlso Station_ID = HUC.Substring(0, 12)) Then
                            StationList &= IIf(StationList = "", "", ",") & "'" & TestNull(dr1.GetValue(0), "") & "'"
                        End If
                    End While
            End Select

            Dim NumRecords As Integer = Project.DB.ExecuteScalar($"SELECT COUNT(*) FROM {Project.FullTableName(TableName)} Dta, {Project.FullTableName(clsProject.enumTableTypes.PCodes)} PCodes WHERE (Dta.PCode=PCodes.PCode OR Dta.PCode IS NULL) AND Station_ID IN ({StationList}) AND Dta.PCode='{PCode}' {IIf(CCode = "(All)", "", String.Format("AND CCode='{0}'", CCode))} AND Date_Time BETWEEN {Project.DB.DateTime(MinDate)} AND {Project.DB.DateTime(MaxDate)}")

            If NumRecords <= 0 Then
                MessageBox.Show(StringFormat("No records were found in {0} for Station ID IN {1}, PCode={2}, CCode={3}, Min Date={4}, Max Date={5}\n\nNote: PCodes in the data table *MUST* have matching records in the PCode Support table.", TableName, Station_ID, PCode, CCode, MinDate, MaxDate), "Comparative Statistics", MessageBoxButtons.OK, MessageBoxIcon.Error)
                app.StatusBar = False
                MainForm.ProgressComplete()
                Return False
            End If

            ReDim Data(NumRecords)

            Dim dr As DbDataReader = Project.DB.ExecuteReader($"SELECT Date_Time,Result,Min_Limit,Dta.RCode,NonDetect_PCode,NonDetect_Result,Detect_Mult,Det_Limit from {Project.FullTableName(TableName)} Dta, {Project.FullTableName(clsProject.enumTableTypes.PCodes)} PCodes, {Project.FullTableName(clsProject.enumTableTypes.RCodes)} RCodes WHERE Dta.PCode=PCodes.PCode AND (Dta.RCode=RCodes.RCode OR Dta.RCode IS NULL) AND Station_ID IN ({StationList}) AND Dta.PCode='{PCode}' {IIf(CCode = "(All)", "", String.Format("AND CCode='{0}'", CCode))} AND Date_Time BETWEEN {Project.DB.DateTime(MinDate)} AND {Project.DB.DateTime(MaxDate)} ORDER BY Date_Time")

            'For i = 1 To NumRecords
            '    dr.Read()
            '    With Data(i)
            '        .DateTime = dr.GetDateTime(0)
            '        .DBValue = dr.GetValue(1)
            '        .Value = .DBValue
            '        .DL = TestNull(dr.GetValue(2), 0)
            '        Dim RCode As String = TestNull(dr.GetValue(3), "")
            '        Dim ND_PCode As Boolean = CBool(TestNull(dr.GetValue(4), "False"))
            '        Dim ND_Result As Boolean = CBool(TestNull(dr.GetValue(5), "False"))
            '        Dim ND_Mult As Double = TestNull(dr.GetValue(6), 0.5)
            '        .IsNonDetect = RCode <> "" AndAlso (ND_PCode Or ND_Result)
            '        If .IsNonDetect Then .Value = .DBValue * ND_Mult
            '        .SampleNum = i
            '    End With
            'Next
            'dr.Close()

            ''it is a bit complicated to make sql query omit bdl (nd) values--just eliminate them here
            'If Not IncludeBDLs Then
            '    For i = NumRecords To 1 Step -1
            '        With Data(i)
            '            If .IsNonDetect Then 'delete it
            '                For jj As Integer = i To NumRecords - 1
            '                    Data(jj) = Data(jj + 1)
            '                Next
            '                NumRecords -= 1
            '            End If
            '        End With
            '    Next
            '    ReDim Preserve Data(NumRecords)
            'End If

            'above code modified 2/3/15 because removal of BDL values did not reset the SampleNum values causing errors later

            Dim SampleNum As Integer = 1
            Dim DataPt As DataType = Nothing

            For i = 1 To NumRecords
                dr.Read()
                With DataPt
                    .DateTime = dr.GetDateTime(0)
                    .DBValue = dr.GetValue(1)
                    .Value = .DBValue
                    .DL = TestNull(dr.GetValue(2), 0)
                    Dim RCode As String = TestNull(dr.GetValue(3), "")
                    Dim ND_PCode As Boolean = CBool(TestNull(dr.GetValue(4), "False"))
                    Dim ND_Result As Boolean = CBool(TestNull(dr.GetValue(5), "False"))
                    Dim ND_Mult As Double = TestNull(dr.GetValue(6), 0.5)
                    Dim DetLimit As Double = TestNull(dr.GetValue(7), 0)
                    .IsNonDetect = RCode <> "" AndAlso (ND_PCode Or ND_Result)
                    If .IsNonDetect Then .Value = .DBValue * ND_Mult
                    If Project.AutoBDL AndAlso RCode = "" AndAlso Not IsDBNull(.DBValue) AndAlso DetLimit <> 0 AndAlso .DBValue < DetLimit Then .IsNonDetect = True
                    .SampleNum = SampleNum
                    If IncludeBDLs OrElse Not .IsNonDetect Then
                        Data(SampleNum) = DataPt
                        SampleNum += 1
                    End If
                End With
            Next
            dr.Close()

            NumRecords = SampleNum - 1
            ReDim Preserve Data(NumRecords)

            ReDim SortedData(NumRecords)
            NumData = 0
            For i = 1 To NumRecords
                If Not Data(i).Outlier Then
                    NumData = NumData + 1
                    SortedData(NumData) = Data(i)
                End If
            Next i
            ReDim Preserve SortedData(NumData)

            'sort data by .Value

            RippleSort(SortedData)

            Dim NumBDL As Integer

            For i = 1 To NumData
                With SortedData(i)
                    .PlottingProb = i / (NumData + 1)
                    .NormalProb = app.WorksheetFunction.NormSInv(.PlottingProb)
                    .Outlier = False
                    If .IsNonDetect Then NumBDL = NumBDL + 1
                End With
            Next i

            Dim loopnum As Integer
            Dim isSorted As Boolean
            Dim NewY As Object
            Dim j, k As Integer
            If NumData - NumBDL >= 2 And NumBDL > 0 Then

                'begin robust BDL analysis (1/20/06)

                '1. do least-squares analysis using only values not flagged as BDL
                '   Note: as of 1/23/06 JCM and I are undecided whether we should
                '         believe the flag or believe the DL. For example, some data
                '         may not be flagged as BDL, yet have a value of 1.5 with a
                '         DL of 2.0. One approach is to ignore the flag, assume it is
                '         a BDL value and therefore not use it in the regression;
                '         alternatively, we can believe the flag and ignore the DL
                '         so that they will be included in the regression. This latter
                '         approach would seem to be preferred, except it can cause
                '         problems with correct sorting and assignment of the plotting
                '         probability if the new (unknown) detection limit is much less
                '         than the assumed one. For example, all flagged BDL values with
                '         a DL of 2.0 will be set to 1.0 (1/2 DL) for sorting, when in
                '         fact they may range from 0.0 to 2.0. If we also have some
                '         data not flagged as BDL, having an unknown DL, with a value of
                '         0.5, we wouldn't know what plotting probability to assign

                'set up arrays containing non-BDL values; will use Excel GROWTH function
                'to get best-fit line to log-transformed data and estimate value of each
                'BDL reading such that it falls on best-fit line

                'here's a wrinkle in the procedure: when you do best-fit line and estimate
                'BDL values, if a data set has multiple detection limits, it is possible that
                'the new set of known and estimated y values will no longer be sorted in
                'increasing order of y values; therefore, allow up to five iterations
                'attempting to get all the known and predicted values in increasing order


                While Not isSorted And loopnum < 5

                    'GROWTH function requires that you pass arrays of known x's and y's and
                    'desired x's--will return array of computed y's at desired x's

                    Dim KnownY(NumData - NumBDL) As Double
                    Dim KnownX(NumData - NumBDL) As Double
                    Dim NewX(NumBDL) As Double

                    j = 0
                    k = 0

                    For i = 1 To NumData
                        With SortedData(i)
                            If .IsNonDetect Then
                                j = j + 1
                                NewX(j) = .NormalProb
                            Else
                                k = k + 1
                                KnownX(k) = .NormalProb
                                KnownY(k) = .Value
                            End If
                        End With
                    Next i

                    Try
                        NewY = app.WorksheetFunction.Growth(KnownY, KnownX, NewX)
                    Catch ex As Exception
                        NewY = Nothing
                    End Try

                    j = 0

                    'now substitute best fit values back into data array

                    If NewY IsNot Nothing Then
                        For i = 1 To NumData
                            With SortedData(i)
                                If .IsNonDetect Then
                                    j = j + 1
                                    .Value = NewY(j)
                                End If
                            End With
                        Next i
                    End If

                    'see if new dataset is still sorted properly
                    isSorted = True
                    For i = 2 To NumData
                        If SortedData(i).Value < SortedData(i - 1).Value Then
                            isSorted = False
                            Exit For
                        End If
                    Next i

                    'if not, resort, reassign plotting probabilities, and loop
                    If Not isSorted Then
                        RippleSort(SortedData)
                        For i = 1 To NumData
                            With SortedData(i)
                                .PlottingProb = i / (NumData + 1)
                                .NormalProb = app.WorksheetFunction.NormSInv(.PlottingProb)
                            End With
                        Next i
                    End If

                    loopnum = loopnum + 1
                End While

            End If

            'end of robust BDL treatment...


            'iteratively loop, finding and removing outliers, until none left
            'note: outlier determination based on log stats
            Do
                CalcStats(SortedData, LogStats, True)
            Loop Until Not NewOutlierFound(SortedData, LogStats)

            CalcStats(SortedData, Stats, False)


            'now that is sorted, compute median values

            If NumData > 0 Then
                If NumData Mod 2 = 0 Then 'even number
                    Stats.Median = (SortedData(NumData / 2).Value + SortedData(NumData / 2 + 1).Value) / 2
                    LogStats.Median = (System.Math.Log(SortedData(NumData / 2).Value) + System.Math.Log(SortedData(NumData / 2 + 1).Value)) / 2
                Else
                    Stats.Median = SortedData(Int(NumData / 2) + 1).Value
                    LogStats.Median = System.Math.Log(SortedData(Int(NumData / 2) + 1).Value)
                End If
            End If

            'write results unformatted to worksheet

            'put detailed data in spreadsheet

            If ObjectContains(wkb.Worksheets, SheetName) Then
                wks = wkb.Worksheets(SheetName)
            Else
                wks = wkb.Worksheets.Add(After:=wkb.Worksheets(wkb.Worksheets.Count))
                wks.Name = SheetName
            End If

            With wks
                .StandardWidth = 12
                .Activate()
            End With

            If Not PasteTable(wks, SheetName & "||" & "<Location ID:|" & Station_ID & "<Parameter:|" & PCode & "<CCode:|" & CCode, "A1", True) Then Return False

            'merge cells containing IDs
            For i = 2 To 4
                With wks.Range(wks.Cells.Item(i, 2), wks.Cells.Item(i, 3))
                    .Merge()
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                End With
            Next i

            If Not PasteTable(wks, "Summary Statistics<" & "|Value|LN(Value)|Arith. Est.<" & "Total Count|" & UBound(Data) & "<" & "Data Count|" & UBound(SortedData) & "<" & "BDL Count|" & Stats.NumBDL & "<" & "# Outliers|" & LogStats.NumOutliers & "<" & "Average|" & Stats.Mean & "|" & LogStats.Mean & "|" & LogStats.ArithEstMean & "<" & "Median|" & Stats.Median & "|" & LogStats.Median & "|" & LogStats.ArithEstMedian & "<" & "Std. Dev.|" & Stats.StDev & "|" & LogStats.StDev & "|" & LogStats.ArithEstStDev & "<" & "CV|" & Stats.CV & "|" & LogStats.CV & "|" & LogStats.ArithEstCV, "A7", True, True) Then Return False

            'reset number format for three cells
            With app.ActiveWindow.RangeSelection
                .Range(wks.Cells.Item(3, 2), wks.Cells.Item(6, 2)).NumberFormat = "General"
            End With

            Results = New System.Text.StringBuilder("Distribution Estimates<P Value|N Dist|LN Dist|Trans<")
            For i = 1 To 5
                Prob = Choose(i, 0.1, 0.25, 0.5, 0.75, 0.9)
                Results.Append(Prob * 100 & "%|" & Choose(i, Stats.Q10, Stats.Q25, Stats.Mean, Stats.Q75, Stats.Q90) & "|" & System.Math.Exp(Choose(i, LogStats.Q10, LogStats.Q25, LogStats.Mean, LogStats.Q75, LogStats.Q90)) & "|" & Choose(i, LogStats.Q10, LogStats.Q25, LogStats.Mean, LogStats.Q75, LogStats.Q90) & "<")
            Next i

            Results.Append("IQR|" & Stats.IQR & "|" & System.Math.Exp(LogStats.Q75) - System.Math.Exp(LogStats.Q25) & "|" & LogStats.IQR & "<")

            If Not PasteTable(wks, Results.ToString, "A19", True, True) Then Return False

            If Not PasteTable(wks, "Confidence Limits<" & "|LN|EXP<" & _
                            "95% t|" & LogStats.t95 & "<" & _
                            "CI|" & LogStats.CI & "<" & _
                            "Std Error|" & LogStats.StdError & "<" & _
                            "Conf. Inter.|" & LogStats.CI & "<" & _
                            "Cox LCL|" & LogStats.CoxLCL & "|" & System.Math.Exp(LogStats.CoxLCL) & "<" & _
                            "Cox UCL|" & LogStats.CoxUCL & "|" & System.Math.Exp(LogStats.CoxUCL), "F19", True, True) Then Return False

            Dim TableComment, ColumnComments As String

            TableComment = "Table of selected data sorted by value after applying robust analysis of BDL values:~" & _
                           "  1. Use a value of 1/2 DL for BDL results, sort and rank data~" & _
                           "  2. Perform log-transformed regression on non-BDL values~" & _
                           "  3. Compute robust replacement values for the BDL data using their rank and the regression results~" & _
                           "  4. Create a robust data series using replacement values (robust) for BDLs and actual values for non-BDL~" & _
                           "  5. Re-sort data set based on the robust data series and assign an updated rank~" & _
                           "  6. Perform a second regression using the non-BDL values and their updated std. normal deviates~" & _
                           "  7. Recompute the robust replacement values for the BDL values based on the second regression results~" & _
                           "  8. Loop until all values are sorted, then compute statistics on the final adjusted data set"

            ColumnComments = "Sample Number|Date-time of observation|Original value stored in database|Specified detection limit for observation|" & _
                             "Is BDL if QCODE value is T or U|Database value or estimate of BDL value using robust BDL analysis (least-squares fit to non-BDL values)|" & _
                             "Natural logarithm of robust value|Plotting probability = 1/(N+1)|Standard Normal Deviate of plotting probability|" & _
                             "Square of logarithm of robust value|" & _
                             "R = Runoff~B = Baseflow~<blank> = No flow data|" & _
                             "0=Groundwater~1=Surface Runoff/Flow~2=Soil~3=Dry Atmospheric Fallout~4=Wet Atmospheric Fallout~5=Pond/Lake Water~6=Accumulated Bottom Sediment~7=Biological~8=Other|" & _
                             "1=Flow wtd composite EMC~2=Time wtd composite EMC~3=Unwtd composite EMC~4=Grab Sample~5=Sediment Sample~6=Biological Sample~7=Continuous meter~8=Manual meter"

            ColumnComments = Replace(ColumnComments, "~", vbLf)

            Results = New System.Text.StringBuilder("Sorted Data<Sample|Date-time|Database Value|Detection Limit|Is BDL?|Robust Value|LN(Robust Value)|" & _
                      "Plotting Probability|Std. Normal Deviate|LN*LN|Flow Type|Sample Media|Sample Type<")

            For i = 1 To UBound(SortedData)
                With SortedData(i)
                    If Not .Outlier Then
                        Results.Append(.SampleNum & "|" & .DateTime & "|" & .DBValue & "|" & .DL & "|" & IIf(.IsNonDetect, "Yes", "No") & "|" & (Max(.Value, 0.00001)) & "|" & System.Math.Log(Max(.Value, 0.00001)) & "|" & .PlottingProb & "|" & .NormalProb & "|" & (System.Math.Log(Max(.Value, 0.00001))) ^ 2 & "<")
                    End If
                End With
            Next i

            If Not PasteTable(wks, Results.ToString, "A29", False, True, True, TableComment, ColumnComments) Then Return False

            'change format on sample number and date columns
            With app.ActiveWindow.RangeSelection
                .Range(wks.Cells.Item(3, 1), wks.Cells.Item(.Rows.Count, 1)).NumberFormat = "General"
                .Range(wks.Cells.Item(3, 2), wks.Cells.Item(.Rows.Count, 2)).NumberFormat = MyDateTimeFormat.ToLower
                .Range(wks.Cells.Item(3, 5), wks.Cells.Item(.Rows.Count, 5)).HorizontalAlignment = Excel.Constants.xlCenter
                .Range(wks.Cells.Item(3, 5), wks.Cells.Item(.Rows.Count, 5)).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "Yes").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                .Range(wks.Cells.Item(3, 11), wks.Cells.Item(.Rows.Count, 13)).HorizontalAlignment = Excel.Constants.xlCenter
                .Range(wks.Cells.Item(3, 11), wks.Cells.Item(.Rows.Count, 13)).NumberFormat = "0"
            End With

            TableComment = "Outliers are determined by comparing the Robust Value to the statistically determined Upper and Lower Fence values."

            Results = New System.Text.StringBuilder("Outliers<Sample|Date-time|Database Value|Detection Limit|Is BDL?|Robust Value|LN(Robust Value)|" & "|||Flow Type|Sample Media|Sample Type<")
            For i = 1 To UBound(SortedData)
                With SortedData(i)
                    If .Outlier Then
                        Results.Append(.SampleNum & "|" & .DateTime & "|" & .DBValue & "|" & .DL & "|" & IIf(.IsNonDetect, "Yes", "No") & "|" & .Value & "|" & System.Math.Log(Max(.Value, 0.00001)) & "||||||<")
                    End If
                End With
            Next i

            'put just below prior table (was left highlighted)
            wks.Range("A29").Offset(app.ActiveWindow.RangeSelection.Rows.Count + 2).Select()
            If Not PasteTable(wks, Results.ToString, app.ActiveCell.Address, False, True, True, TableComment, ColumnComments) Then Return False

            'change format on sample number and date columns
            With app.ActiveWindow.RangeSelection
                .Range(wks.Cells.Item(3, 1), wks.Cells.Item(.Rows.Count, 1)).NumberFormat = "General"
                .Range(wks.Cells.Item(3, 2), wks.Cells.Item(.Rows.Count, 2)).NumberFormat = MyDateTimeFormat.ToLower
                .Range(wks.Cells.Item(3, 5), wks.Cells.Item(.Rows.Count, 5)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Range(wks.Cells.Item(3, 11), wks.Cells.Item(.Rows.Count, 13)).HorizontalAlignment = Excel.Constants.xlCenter
                .Range(wks.Cells.Item(3, 11), wks.Cells.Item(.Rows.Count, 13)).NumberFormat = "0"
            End With

            'format worksheet for printing

            With wks.PageSetup
                .PrintArea = ""
                .Orientation = Excel.XlPageOrientation.xlLandscape
                .PrintHeadings = False
                .LeftMargin = app.InchesToPoints(0.5)
                .RightMargin = app.InchesToPoints(0.5)
                .TopMargin = app.InchesToPoints(0.5)
                .BottomMargin = app.InchesToPoints(0.75)
                .FooterMargin = app.InchesToPoints(0.5)
                .LeftFooter = "&07WRDB Version " & My.Application.Info.Version.ToString
                .CenterFooter = "&07&D &T"
                .RightFooter = "&07&F [&A]"
            End With

            wks.Cells._Default(1, 1).Select()

            'set outlier flag in original data using setting in sorted data so calling routine can use
            For i = 1 To UBound(SortedData)
                With SortedData(i)
                    Data(.SampleNum).Outlier = .Outlier
                    Data(.SampleNum).Value = .Value 'so passes back robust values
                End With
            Next i
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Create a sub-table in a spreadsheet by pasting text and formatting
    ''' Embedded | character replaced with tab, "less-than" replaced with crlf
    ''' </summary>
    Private Function PasteTable(ByRef wks As Excel.Worksheet, ByRef Text As String, ByRef UpperLeft As String, Optional ByRef RowHeadings As Boolean = False, Optional ByRef ColHeadings As Boolean = False, Optional ByRef TableHeading As Boolean = True, Optional ByRef TableComment As String = "", Optional ByRef ColComments As String = "", Optional ByRef RowComments As String = "") As Boolean
        Try
            Try
                My.Computer.Clipboard.Clear()
                My.Computer.Clipboard.SetText(Replace(Replace(Text, "|", vbTab), "<", vbCrLf))
            Catch ex As Exception
                WarningMsg("Unable to paste the table to the clipboard. The table size was: " & Text.Length, , ex)
                Return False
            End Try
            Dim r, c As Integer
            Dim Cmt() As String
            Dim i As Integer
            With wks
                .Range(UpperLeft).Select()
                .Paste()
                With app.ActiveWindow.RangeSelection
                    'search for formulas and create
                    If Text.Contains("=") Then
                        For r = 1 To .Rows.Count
                            For c = 1 To .Columns.Count
                                If wks.Cells.Item(r, c).value Like "=*" Then
                                    wks.Cells.Item(r, c).Formula = wks.Cells.Item(r, c)
                                End If
                            Next c
                        Next r
                    End If

                    .Borders.LineStyle = Excel.XlLineStyle.xlContinuous
                    .BorderAround(Excel.XlLineStyle.xlDouble)
                    .NumberFormat = "0.000"
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

                    If TableHeading Then
                        With .Range(wks.Cells.Item(1, 1), wks.Cells.Item(1, .Columns.Count))
                            .Merge()
                            .BorderAround(Excel.XlLineStyle.xlDouble)
                            .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                            .Font.Bold = True
                        End With
                    End If

                    If RowHeadings Then
                        With .Range(wks.Cells.Item(IIf(TableHeading, 2, 1), 1), wks.Cells.Item(.Rows.Count, 1))
                            .Font.Bold = True
                            .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                        End With
                    End If
                    If ColHeadings Then
                        With .Range(wks.Cells.Item(IIf(TableHeading, 2, 1), 1), wks.Cells.Item(IIf(TableHeading, 2, 1), .Columns.Count))
                            .Font.Bold = True
                            .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                            .WrapText = True
                        End With
                    End If


                    If TableComment <> "" And TableHeading Then
                        With .Range(wks.Cells.Item(1, 1), wks.Cells.Item(1, 1)).AddComment
                            .Text(TableComment)
                            .Shape.TextFrame.AutoSize = True
                        End With
                    End If

                    If ColComments <> "" And ColHeadings Then
                        Cmt = Split(ColComments, "|")
                        r = IIf(TableHeading, 2, 1)
                        For c = 1 To Min(.Columns.Count, UBound(Cmt) + 1)
                            If Cmt(c - 1) <> "" Then
                                With .Range(wks.Cells.Item(r, c), wks.Cells.Item(r, c)).AddComment
                                    .Text(Cmt(c - 1))
                                    .Shape.TextFrame.AutoSize = True
                                End With
                            End If
                        Next c
                    End If

                    If RowComments <> "" And RowHeadings Then
                        Cmt = Split(RowComments, "|")
                        c = 1
                        For i = 1 To Math.Min(.Rows.Count - IIf(TableHeading, 1, 0) - IIf(ColHeadings, 1, 0), UBound(Cmt) + 1)
                            r = i + IIf(TableHeading, 1, 0) + IIf(ColHeadings, 1, 0)
                            If Cmt(i - 1) <> "" Then
                                With .Range(wks.Cells.Item(r, c), wks.Cells.Item(r, c)).AddComment
                                    .Text(Cmt(i - 1))
                                    .Shape.TextFrame.AutoSize = True
                                End With
                            End If
                        Next i
                    End If
                End With
            End With
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Function

    ''' <summary>
    ''' Calculate statistics and place in data structure
    ''' </summary>
    Private Sub CalcStats(ByRef Data() As DataType, ByRef Stats As StatType, ByRef LogTransform As Boolean)
        Try
            Dim i As Integer
            Dim SumX, LogData, SumX2 As Double

            Dim CoxCI As Double
            With Stats
                .Num = 0
                SumX = 0
                SumX2 = 0
                .NumOutliers = 0
                .NumBDL = 0
                .Min = 1.0E+38
                .Max = -.Min
                For i = 1 To UBound(Data)
                    If Data(i).Outlier Then
                        .NumOutliers = .NumOutliers + 1
                    Else
                        LogData = IIf(LogTransform, System.Math.Log(Max(Data(i).Value, 0.00001)), Data(i).Value)
                        .Num = .Num + 1
                        SumX = SumX + LogData
                        SumX2 = SumX2 + LogData ^ 2
                        If LogData < .Min Then .Min = LogData
                        If LogData > .Max Then .Max = LogData
                    End If
                    If Data(i).IsNonDetect Then .NumBDL = .NumBDL + 1
                Next i
                .Mean = SumX / .Num
                If .Num > 1 Then
                    If System.Math.Abs(.Min - .Max) < 0.00001 Then
                        .StDev = 0
                        .Q10 = .Mean
                        .Q25 = .Mean
                        .Q75 = .Mean
                        .Q90 = .Mean
                    Else
                        .StDev = System.Math.Sqrt((.Num * SumX2 - SumX ^ 2) / (.Num * (.Num - 1.0#)))
                        .Q10 = app.WorksheetFunction.NormInv(0.1, .Mean, .StDev)
                        .Q25 = app.WorksheetFunction.NormInv(0.25, .Mean, .StDev)
                        .Q75 = app.WorksheetFunction.NormInv(0.75, .Mean, .StDev)
                        .Q90 = app.WorksheetFunction.NormInv(0.9, .Mean, .StDev)
                    End If
                    .CV = .StDev / IIf(.Mean = 0, 0.0001, .Mean)
                    .IQR = .Q75 - .Q25
                    .LFence = .Q25 - 1.5 * .IQR
                    .UFence = .Q75 + 1.5 * .IQR
                    .StdError = .StDev / System.Math.Sqrt(.Num)
                    If .Num > 2 Then .t95 = app.WorksheetFunction.TInv(0.05, .Num - 2)
                    .CI = .StdError * .t95
                    .LCL = .Mean - .CI
                    .UCL = .Mean + .CI

                    '1/30/06: per paper by Ulf Olsson (http://www.amstat.org/publications/jse/v13n1/olsson.html)
                    'use Modified Cox method to compute upper and lower confidence limits
                    'also, use Olsson's paper to compute arithmetic estimates of statistics
                    If LogTransform Then
                        CoxCI = .t95 * System.Math.Sqrt(.StDev ^ 2 / .Num + .StDev ^ 4 / (2 * (.Num - 1)))
                        .CoxLCL = .Mean + .StDev ^ 2 / 2 - CoxCI
                        .CoxUCL = .Mean + .StDev ^ 2 / 2 + CoxCI
                        .ArithEstMean = System.Math.Exp(.Mean + 0.5 * .StDev ^ 2)
                        .ArithEstMedian = System.Math.Exp(.Mean)
                        .ArithEstCV = System.Math.Sqrt(System.Math.Exp(.StDev ^ 2) - 1)
                        .ArithEstStDev = .ArithEstMean * .ArithEstCV
                    End If

                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    ''' <summary>
    ''' Iteratively called to determine outliers
    ''' </summary>
    Private Function NewOutlierFound(ByRef Data() As DataType, ByRef Stats As StatType) As Boolean
        Try
            Dim i As Integer

            NewOutlierFound = False
            With Stats
                If .StDev = 0 Then Exit Function
                For i = 1 To UBound(Data)
                    If Not Data(i).Outlier And (System.Math.Log(Max(Data(i).Value, 0.00001)) < .LFence Or System.Math.Log(Max(Data(i).Value, 0.00001)) > .UFence) Then
                        Data(i).Outlier = True
                        NewOutlierFound = True
                    End If
                Next i
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try

    End Function

    '=======================================================================================
    ' following routines are general purpose methods used throughout this module
    '=======================================================================================

    ''' <summary>
    ''' Convert list of single digit codes to appropriate comma separated list suitable for SQL IN query
    ''' </summary>
    Private Function InList(ByRef List As String, ByRef Numeric As Boolean) As String
        Try
            Dim i As Integer
            Dim TempList As String = ""

            If List = "" Then
                'put bogus value in list so SQL doesn't generate error
                TempList = IIf(Numeric, "123456789", "'qwerty'")
            Else
                For i = 1 To Len(List)
                    TempList = TempList & IIf(i = 1, "", ",") & IIf(Numeric, "", "'") & Mid(List, i, 1) & IIf(Numeric, "", "'")
                Next i
            End If
            Return TempList
        Catch ex As Exception
            ErrorMsg(, ex)
            Return ""
        End Try
    End Function

    ''' <summary>
    ''' Perform simple sort on user-defined data type called DataType (for plotting probs)
    ''' </summary>
    Private Sub RippleSort(ByRef DataArray() As DataType)
        Try
            Dim i, j As Integer
            Dim TempData As DataType

            For i = UBound(DataArray) To LBound(DataArray) + 1 Step -1
                For j = LBound(DataArray) + 1 To i
                    If DataArray(j - 1).Value > DataArray(j).Value Then
                        TempData = DataArray(j - 1)
                        DataArray(j - 1) = DataArray(j)
                        DataArray(j) = TempData
                    End If
                Next j
            Next i
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Friend Function ObjectContains(ByRef Collection As Object, ByVal Item As String) As Boolean
        For i As Integer = 1 To Collection.count
            If Collection(i).Name = Item Then Return True
        Next
        Return False
    End Function

    Friend Sub HelpAcquire()
        MessageBox.Show(StringFormat("This utility is designed to extract selected data from the FL BMP Access database, perform multiple statistical analyses on the data, and create a new output spreadsheet that contains the extracted data and analysis results.\n\nThe approach taken in the development of the FL BMP Database is to use statistical characterizations of the Dataset 1 and Dataset 2 concentrations to evaluate the effectiveness of the BMPs. The Event Mean Concentration method describes the statistical distributions of the upstream and downstream water quality to determine if there are statistically significant differences, as well as confidence limits about the expected average values. This method provides standard descriptive statistics, box & whisker charts, normal probability charts and time-series plots of the transformed data for both the Dataset 1 and 2 data. This information is used to demonstrate the differences in the mean EMCs, including confidence intervals, as well as the performance of the BMP throughout the range of influent and effluent EMCs."), "Help with EMC Analysis Utility", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

End Module
